﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Autorun
{
	[Serializable()]
	public class TaskConfigurationCollection : MarshalByRefObject
	{
		#region "Singleton Design-Pattern"
		private static TaskConfigurationCollection _instance = new TaskConfigurationCollection();

		public static TaskConfigurationCollection Instance
		{
			get { return _instance; }
		}
		#endregion

		#region "Properties"
		public List<TaskConfigurationItem> Items
		{
			get
			{
				string query;
				DataTable dt;
				List<TaskConfigurationItem> rc;

				rc = new List<TaskConfigurationItem>();
				query = "SELECT *, (SELECT COUNT(*) FROM tblSchedule WHERE tskId=fktskId) AS tskUsage FROM tblTask ORDER BY tskCaption";
				dt = ExecuteDataTable(query);
				foreach (DataRow dr in dt.Rows)
					rc.Add(new TaskConfigurationItem(dr));

				return rc;
			}
		}

		#endregion

		#region "Procedures"
		public TaskConfigurationItem GetTaskInformation(int taskId)
		{
			TaskConfigurationItem rc;

			rc = new TaskConfigurationItem(taskId);

			return rc;
		}

		public TaskConfigurationItem Add(string taskCaption, string assemblyName, string assemblyPath, string className)
		{
			TaskConfigurationItem rc;

			rc = new TaskConfigurationItem(taskCaption, assemblyName, assemblyPath, className);
			rc.Update(null);

			// === Manage Task Parameters =============================================
			// === Retrieve the configuration parameters ==============================

			Verify(rc);

			return rc;

			//return int.Parse(rc2.ToString());
		}

		public void Verify(int taskId)
		{
			TaskConfigurationItem item;

			item = new TaskConfigurationItem(taskId);
			Verify(item);
		}

		private void Verify(TaskConfigurationItem rc)
		{
			string query;
			int count;
			TaskBase task;

			task = (TaskBase)ServerManagement.Instance.FileAccess.LoadTask(rc.Path, rc.AssemblyName, rc.ClassName);

			query = string.Format("UPDATE tblConfig SET cfgDirty=1 WHERE fktskId={0}", rc.Id);
			ExecuteNonQuery(query);

			foreach (DictionaryEntry item in task.Parameters)
			{
				query = string.Format("SELECT COUNT(*) FROM tblConfig WHERE fktskId={0} AND cfgKey={1}",
						rc.Id,
						SqlConvert.SqlString(task.Parameters.Keys));
				count = (int)ExecuteScalar(query);
				if (count == 0)
					query = string.Format("INSERT INTO tblConfig(fktskId, cfgKey, cfgValue) VALUES ({0}, {1}, {2})",
							rc.Id,
							SqlConvert.SqlString(item.Key),
							SqlConvert.SqlString(item.Value));
				else
					query = string.Format("UPDATE tblConfig SET cfgDirty=0 WHERE fktskId={0} AND cfgKey={1}",
							rc.Id,
							SqlConvert.SqlString(item.Key));
				ExecuteNonQuery(query);
			}

			// Remove unused parameters
			query = string.Format("DELETE FROM tblConfig WHERE fktskId={0} AND cfgDirty=1", rc.Id);
			ExecuteNonQuery(query);
		}

		public void Remove(int taskId, bool force)
		{
			int rc;
			string query;

			query = string.Format("SELECT ISNULL((SELECT COUNT(*) FROM tblLog WHERE fktskId={0}),0)", taskId);
			rc = (int)ExecuteScalar(query);

			if (rc != 0)
				if (force == true)
				{
					query = string.Format("DELETE FROM tblLog WHERE fktskId={0}", taskId);
					ExecuteDataSet(query);
				}
				else
					return;
			
			query = string.Format("SELECT ISNULL((SELECT COUNT(*) FROM tblSchedule WHERE fktskId={0}),0)",
					taskId);
			rc = (int)ExecuteScalar(query);

			if (rc != 0)
				if (force == true)
				{
					query = string.Format("DELETE FROM tblSchedule WHERE fktskId={0}", taskId);
					ExecuteNonQuery(query);
				}
				else
					return;

			query = string.Format("DELETE FROM tblConfig WHERE fktskId={0}", taskId);
			ExecuteNonQuery(query);

			query = string.Format("DELETE FROM tblTask WHERE tskId={0}", taskId);
			ExecuteNonQuery(query);
		}
		#endregion

		#region "Data Access Methods"
		private void ExecuteNonQuery(string query)
		{
			SqlConnection cn;
			SqlCommand cmd;

			cn = new SqlConnection(Properties.Settings.Default.ConnectionString);

			try
			{
				cn.Open();
				cmd = cn.CreateCommand();
				cmd.CommandText = query;
				cmd.ExecuteNonQuery();
				cmd.Connection = null;
				cmd = null;
			}
			finally
			{
				cn.Close();
			}
		}

		private object ExecuteScalar(string query)
		{
			SqlConnection cn;
			SqlCommand cmd;
			object rc = null;

			cn = new SqlConnection(Properties.Settings.Default.ConnectionString);

			try
			{
				cn.Open();
				cmd = cn.CreateCommand();
				cmd.CommandText = query;
				rc = cmd.ExecuteScalar();
				cmd.Connection = null;
				cmd = null;
			}
			finally
			{
				cn.Close();
			}

			return rc;
		}

		private DataTable ExecuteDataTable(string query)
		{
			SqlDataAdapter da;
			DataSet rc;

			da = new SqlDataAdapter(query, Properties.Settings.Default.ConnectionString);
			rc = new DataSet();
			da.Fill(rc);
			return rc.Tables.Count != 0 ? rc.Tables[0] : null;
		}

		private DataSet ExecuteDataSet(string query)
		{
			SqlDataAdapter da;
			DataSet rc;

			da = new SqlDataAdapter(query, Properties.Settings.Default.ConnectionString);
			rc = new DataSet();
			da.Fill(rc);
			return rc;
		}
		#endregion

	}
}
